package org.price.sql.repo.session.db.dialect;

import java.security.MessageDigest;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.price.sql.repo.document.model.PropertyInfo;
import org.price.sql.repo.documents.config.Index;
import org.price.sql.repo.documents.config.ObjectFactory;
import org.price.sql.repo.documents.config.Property;
import org.price.sql.repo.documents.config.SqlType;
import org.price.sql.repo.documents.config.Unique;
import org.price.sql.repo.session.core.DocumentInfo;

import sun.misc.BASE64Encoder;

@SuppressWarnings("unchecked")
public abstract class DialectAbstract implements
        Dialect<DocumentInfo, PropertyInfo> {
    private static final Logger LOG = Logger.getLogger(DialectAbstract.class);

    private final Connection connection;

    protected String currentSchema;

    protected String quoteString;

    protected String catalogSeparator;

    protected DocumentInfo[] tables;

    protected String createTableString;

    protected String dropTableString;

    protected String dropIndexString;

    protected String createColumnString;

    protected String createIndexString;

    protected String createUniqueString;

    protected String dropColumnString;

    protected String dropUniqueString;

    protected String createPrimaryKeyString;

    protected final static ObjectFactory factory = new ObjectFactory();

    public DialectAbstract(Connection connection) throws SQLException {
        this.connection = connection;

        currentSchema = getConnection().getCatalog();
        LOG.info("Current Catalog: " + currentSchema);
        quoteString = getConnection().getMetaData().getIdentifierQuoteString();
        LOG.info("Database quote symbol: " + quoteString);
        catalogSeparator = getConnection().getMetaData().getCatalogSeparator();
        LOG.info("Catalog separator symbol: " + catalogSeparator);

    }

    protected void buildTableCreateString() {
        StringBuilder ctSb = new StringBuilder("CREATE TABLE ");
        ctSb.append(quoteString);
        ctSb.append("%s");
        ctSb.append(quoteString);
        ctSb.append(" ()");

        createTableString = ctSb.toString();
    }

    protected abstract void buildIndexCreateString();

    protected abstract void buildUniqueCreateString();

    protected void buildTableDropString() {
        StringBuilder dtSb = new StringBuilder("DROP TABLE ");
        dtSb.append(quoteString);
        dtSb.append("%s");
        dtSb.append(quoteString);

        dropTableString = dtSb.toString();
    }

    protected void buildIndexDropString() {
        StringBuilder dtSb = new StringBuilder("DROP INDEX ");
        dtSb.append(quoteString);
        dtSb.append("%s");
        dtSb.append(quoteString);

        dropTableString = dtSb.toString();
    }

    protected void buildUniqueDropString() {
        StringBuilder dtSb = new StringBuilder("ALTER TABLE ");
        dtSb.append(quoteString);
        dtSb.append("%s");
        dtSb.append(quoteString);
        dtSb.append(" DROP CONSTRAINT ");
        dtSb.append(quoteString);
        dtSb.append("%s");
        dtSb.append(quoteString);

        dropUniqueString = dtSb.toString();
    }

    protected void buildColumnCreateString() {

        StringBuilder sb = new StringBuilder("ALTER TABLE ");
        sb.append(quoteString);
        sb.append("%s");
        sb.append(quoteString);
        sb.append(" ADD COLUMN ");
        sb.append(quoteString);
        sb.append("%s");
        sb.append(quoteString);
        sb.append(" ");
        sb.append("%s");

        createColumnString = sb.toString();
    }

    protected void buildColumnDropString() {
        StringBuilder dtSb = new StringBuilder("ALTER TABLE ");
        dtSb.append(quoteString);
        dtSb.append("%s");
        dtSb.append(quoteString);
        dtSb.append(" DROP COLUMN ");
        dtSb.append(quoteString);
        dtSb.append("%s");
        dtSb.append(quoteString);

        dropColumnString = dtSb.toString();
    }

    protected void buildCreatePrimaryKeyString() {
        StringBuilder dtSb = new StringBuilder("ALTER TABLE ");
        dtSb.append(quoteString);
        dtSb.append("%s");
        dtSb.append(quoteString);
        dtSb.append(" ADD CONSTRAINT ");
        dtSb.append(quoteString);
        dtSb.append("%s");
        dtSb.append(quoteString);
        dtSb.append("(%s)");

        createPrimaryKeyString = dtSb.toString();
    }


    public Connection getConnection() {
        return connection;
    }

    public void reset() {
        tables = null;
    }

    public int createTable(String name) throws SQLException {
        if (createTableString == null) {
            buildTableCreateString();
        }

        LOG.info("Creating table " + name);

        String uStr = String.format(createTableString,
                getNormalizedTableName(name));
        PreparedStatement pstm = getConnection().prepareStatement(uStr);
        int ret = pstm.executeUpdate();
        pstm.close();

        return ret;
    }

    public int dropTable(String name) throws SQLException {
        if (dropTableString == null) {
            buildTableDropString();
        }

        LOG.info("Dropping table " + name);
        String uStr = String.format(dropTableString,
                getNormalizedTableName(name));
        PreparedStatement pstm = getConnection().prepareStatement(uStr);
        int ret = pstm.executeUpdate();
        pstm.close();

        return ret;
    }

    public int dropIndex(String name, String tableName) throws SQLException {
        if (dropIndexString == null) {
            buildIndexDropString();
        }

        LOG.info("Dropping index " + name);
        String uStr = String.format(dropIndexString,
                getNormalizedConstraintName(name));
        PreparedStatement pstm = getConnection().prepareStatement(uStr);
        int ret = pstm.executeUpdate();
        pstm.close();

        return ret;
    }

    public DocumentInfo[] getTables() throws SQLException {
        if (tables == null) {

            ResultSet rs = getConnection().getMetaData().getTables(null, null,
                    null, new String[] { "TABLE" });
            List<DocumentInfo> ltables = new ArrayList<DocumentInfo>(
                    rs.getFetchSize());
            while (rs.next()) {
                DocumentInfo dInfo = new DocumentInfo();
                dInfo.setType(rs.getString("TABLE_NAME"));
                ltables.add(dInfo);
            }
            rs.close();

            addProperties(ltables);
            addIndexes(ltables);
            addUniques(ltables);

            tables = ltables.toArray(new DocumentInfo[0]);
        }

        return tables;
    }

    protected void addUniques(List<DocumentInfo> ltables) throws SQLException {
        for (DocumentInfo dInfo : ltables) {
            addUniques(dInfo);
        }
    }

    protected void addUniques(DocumentInfo dInfo) throws SQLException {
        Map<String, Unique> uniques = new Hashtable<String, Unique>();

        ResultSet rs = getConnection().getMetaData().getIndexInfo(
                getConnection().getCatalog(), null, dInfo.getType(), true,
                false);
        while (rs.next()) {
            String name = rs.getString("INDEX_NAME");
            if (name != null) {
                Unique unq;
                String column = rs.getString("COLUMN_NAME");
                unq = uniques.get(column);
                if (unq == null) {
                    unq = factory.createUnique();
                }

                unq.getProperty().add(column);
                unq.setName(name);
                uniques.put(column, unq);
            }
        }

        rs.close();
        dInfo.setUniques(uniques.values().toArray(new Unique[0]));
    }

    protected void addIndexes(List<DocumentInfo> ltables) throws SQLException {
        for (DocumentInfo dInfo : ltables) {
            addIndexes(dInfo);
        }
    }

    protected void addIndexes(DocumentInfo dInfo) throws SQLException {
        Map<String, Index> indexes = new Hashtable<String, Index>();

        ResultSet rs = getConnection().getMetaData().getIndexInfo(
                getConnection().getCatalog(), null, dInfo.getType(), false,
                false);
        while (rs.next()) {
            String name = rs.getString("INDEX_NAME");
            if (name != null) {
                Index idx;
                String column = rs.getString("COLUMN_NAME");
                idx = indexes.get(column);
                if (idx == null) {
                    idx = factory.createIndex();
                }

                idx.getProperty().add(column);
                idx.setName(name);
                indexes.put(column, idx);
            }
        }

        rs.close();
        dInfo.setIndexes(indexes.values().toArray(new Index[0]));
    }

    protected void addProperties(List<DocumentInfo> ltables)
            throws SQLException {
        for (DocumentInfo dInfo : ltables) {
            addProperties(dInfo);
        }
    }

    protected void addProperties(DocumentInfo dInfo) throws SQLException {

        ResultSet rs = getConnection().getMetaData().getColumns(null, null,
                dInfo.getType(), null);
        List<PropertyInfo> props = new ArrayList<PropertyInfo>();

        while (rs.next()) {
            addProperty(props, rs);
        }

        rs.close();

        dInfo.setProperties(props.toArray(new PropertyInfo[0]));
    }

    protected void addProperty(List<PropertyInfo> props, ResultSet rs)
            throws SQLException {
        PropertyInfo<Object> property = new PropertyInfo<Object>();
        props.add(property);
        Property prop = factory.createProperty();
        property.setDocProperty(prop);
        prop.setName(rs.getString("COLUMN_NAME"));
        prop.setLength(rs.getInt("COLUMN_SIZE"));
        prop.setNullable(rs.getInt("NULLABLE") != 0);

        SqlType type = getSqlTypeFromJDBCType(rs.getInt("DATA_TYPE"));
        prop.setType(type);
    }

    protected SqlType getSqlTypeFromJDBCType(int type) {
        switch (type) {
        case Types.VARCHAR:
            return SqlType.VARCHAR;
        case Types.INTEGER:
            return SqlType.INTEGER;
        case Types.BIGINT:
            return SqlType.LONG;
        case Types.BIT:
        case Types.BOOLEAN:
            return SqlType.BOOLEAN;
        case Types.BLOB:
            return SqlType.BLOB;
        }

        return null;
    }

    public int createColumn(String tableName, PropertyInfo property)
            throws SQLException {
        int ret = -1;
        if (createColumnString == null) {
            buildColumnCreateString();
        }

        StringBuilder logSb = new StringBuilder("Creating in table \"");
        logSb.append(tableName).append("\" column \"").append(
                property.getDocProperty().getName());
        logSb.append("\" type ").append(property.getDocProperty().getType());

        LOG.info(logSb);

        PreparedStatement pStm = null;
        pStm = getConnection().prepareStatement(
                getSqlStringFromSqlType(getNormalizedTableName(tableName),
                        property));
        ret = pStm.executeUpdate();
        pStm.close();

        return ret;
    }

    public abstract int createIndex(String name, String tableName, String type,
            String... propNames) throws SQLException;

    public abstract int createUnique(String name, String tableName,
            String type, String... propNames) throws SQLException;

    public int dropColumn(String tableName, PropertyInfo property)
            throws SQLException {
        if (dropColumnString == null) {
            buildColumnDropString();
        }

        LOG.info("Dropping column " + property.getDocProperty().getName());
        String uStr = String.format(
                dropColumnString,
                getNormalizedTableName(tableName),
                getNormalizedConstraintName(property.getDocProperty().getName()));

        PreparedStatement pStm = null;
        pStm = getConnection().prepareStatement(uStr);
        int ret = pStm.executeUpdate();
        pStm.close();

        return ret;
    }

    public int dropUnique(String name, String tableName) throws SQLException {
        if (dropUniqueString == null) {
            buildUniqueDropString();
        }

        LOG.info("Dropping unique constraint " + tableName);
        String uStr = String.format(dropUniqueString,
                getNormalizedTableName(tableName),
                getNormalizedConstraintName(name));

        PreparedStatement pStm = null;
        pStm = getConnection().prepareStatement(uStr);
        int ret = pStm.executeUpdate();
        pStm.close();

        return ret;
    }

    public abstract String getSqlStringFromSqlType(String tableName,
            PropertyInfo prop) throws SQLException;

    public String getNormalizedConstraintName(String originalName)
            throws SQLException {
        int length = getConnection().getMetaData().getMaxColumnNameLength();
        return getNormalizedName(originalName, length);
    }

    public String getNormalizedColumnName(String originalName)
            throws SQLException {
        int length = getConnection().getMetaData().getMaxColumnNameLength();
        return getNormalizedName(originalName, length);
    }

    public String getNormalizedTableName(String originalName)
            throws SQLException {
        int length = getConnection().getMetaData().getMaxTableNameLength();
        return getNormalizedName(originalName, length);
    }

    public String getNormalizedName(String originalName, int length)
            throws SQLException {

        if (originalName.length() > length) {
            StringBuilder newName = new StringBuilder(originalName.substring(0,
                    length / 2 - 1));
            newName.append('_');
            MessageDigest md5;
            try {
                md5 = MessageDigest.getInstance("MD5");
            } catch (Exception e) {
                throw new SQLException(e.getMessage());
            }

            int index = 0;
            String hash = new BASE64Encoder().encode(md5.digest(originalName.getBytes()));
            while (newName.length() < length) {
                newName.append(hash.charAt(index));
                index++;
            }

            return newName.toString();
        }

        return originalName;
    }

    public int createPrimaryKey(String tableName, String name, String... props)
            throws SQLException {
        return 0;
    }

    public int createForeignKey(String name, String primaryTable,
            String ptProperty, String secondaryTable, String stProperty)
            throws SQLException {
        return 0;
    }

    public int dropForeignKey(String tableName, String name)
            throws SQLException {
        return 0;
    }

    public int dropPrimaryKey(String tableName, String name)
            throws SQLException {
        return 0;
    }

    public Object setValue(ResultSet rs, SqlType type) {

        return null;
    }

    public Object getValue(ResultSet rs, SqlType type) {

        return null;
    }

}
